use master
go

create database TestInfo
on
(
	name='TestInfo',
	filename='D:\TestInfo.mdf',
	size=5MB,
	maxsize=50MB,
	filegrowth=1MB
)
log on
(
	name='TestInfo_log',
	filename='D:\TestInfo_log.ldf',
	size=5MB,
	maxsize=50MB,
	filegrowth=1MB
)
go

use TestInfo

create table orders
(
orderId int primary key identity,
orderDate date
)

create table orderItem
(
ItemiD int identity not null,
orderId int references orders(orderId),
itemType nvarchar(10) not null,
itemName nvarchar(10) not null,
theNumber int not null,
theMoney money not null
)

insert into orders values
('2008-01-12'),
('2008-02-10'),
('2008-02-15'),
('2008-03-10')

insert into orderItem(orderId,itemType,itemName,theNumber,theMoney) values 
(1,'文具','笔',72,2),
(1,'文具','尺',10,1),
(1,'体育用品','篮球',1,56),
(2,'文具','笔',36,2),
(2,'文具','固体胶',20,3),
(2,'日常用品','透明胶',2,1),
(2,'体育用品','羽毛球',20,3),
(3,'文具','订书机',20,3),
(3,'文具','订书针',10,3),
(3,'文具','裁纸刀',5,5),
(4,'文具','笔',20,2),
(4,'文具','信纸',50,1),
(4,'日常用品','毛巾',4,5),
(4,'日常用品','透明胶',30,1),
(4,'体育用品','羽毛球',20,3)

select 
	a.*,b.orderDate 
from 
	orderItem a
join
	orders b
on
	a.orderId = b.orderId

select * from orderItem
-- 
select * from orders

select 
	a.orderId,b.orderDate,a.itemType,a.itemName
from
	orderItem a
join
	orders b
on
	a.orderId = b.orderId
where
	theNumber > 50

select 
	a.*,b.orderDate
from
	orderItem a
join
	orders b
on
	a.orderId = b.orderId

select
	a.*,b.orderDate
from
	orderItem a
join
	orders b
on
	a.orderId = b.orderId
where
	theNumber > 5 and theMoney > 50

select
	orderId 订单编号, count(orderId) 订购产品数
from
	orderItem
group by
	orderId

select
	orderId 订单编号,itemType 产品类别,count(*) 订购次数,sum(theNumber) 总数量
from
	orderItem
group by
	orderId,itemId



